package cn.pfz.Service.impl;

import cn.pfz.Service.IEmployeeSerives;
import cn.pfz.domain.Department;
import cn.pfz.domain.Employee;
import cn.pfz.domain.EmployeeRole;
import cn.pfz.mapper.EmployeeMapper;
import cn.pfz.query.EmployeeQuery;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.shiro.crypto.hash.Md5Hash;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.List;

@Service
public class IEmployeeSerivesimpl implements IEmployeeSerives {
    @Autowired
    private EmployeeMapper employeeMapper;

    @Override
    public int deleteByPrimaryKey(Long id) {
        return employeeMapper.deleteByPrimaryKey(id);
    }

    @Override
    public int insert(Employee record) {

        if(record.getPassword()!=null){
            Md5Hash md5Hash = new Md5Hash(record.getPassword(),record.getName());
            record.setPassword(md5Hash.toString());
        }

        return employeeMapper.insert(record);
    }

    @Override
    public Employee selectByPrimaryKey(Long id) {
        return employeeMapper.selectByPrimaryKey(id);
    }

    @Override
    public List<Employee> selectAll() {
        return employeeMapper.selectAll();
    }

    @Override
    public int updateByPrimaryKey(Employee record) {
        if(record.getPassword()!=null){
            Md5Hash md5Hash = new Md5Hash(record.getPassword(),record.getName());
            record.setPassword(md5Hash.toString());
        }
        return employeeMapper.updateByPrimaryKey(record);
    }

    @Override
    public PageInfo<Employee> list(EmployeeQuery qo) {
        //通过pageHelper进行设置分页及排序
        PageHelper.startPage(qo.getCurrentPage(),qo.getPageSize(),"id asc");
        //在Sql中不需要limit
        List<Employee> Employees = employeeMapper.selectRusult(qo);
        PageInfo<Employee> PageInfo = new PageInfo(Employees);
        return PageInfo;
    }

    @Override
    public void batchDeleteByEmployeeIds(Long[] ids) {
        employeeMapper.batchDeleteByEmployeeIds(ids);
    }

    @Override
    public Employee selectByUsername(String username) {
        return employeeMapper.selectByUsername(username);
    }

    @Override
    public Workbook getWrokbook() {
        //创建一个excel表格
        Workbook workbook = new HSSFWorkbook();

        //创建工作区
        Sheet st = workbook.createSheet("员工表");
        Row row = st.createRow(0);
        row.createCell(0).setCellValue("编号");
        row.createCell(1).setCellValue("用户名");
        row.createCell(2).setCellValue("姓名");
        row.createCell(3).setCellValue("密码");
        row.createCell(4).setCellValue("邮箱");
        row.createCell(5).setCellValue("年龄");
        //获取要导出的数据
        List<Employee> employees = employeeMapper.selectAll();
        //循环写入表格
        for (int i = 0; i < employees.size(); i++) {

            Employee employee = employees.get(i);
            Row rows = st.createRow(i+1);
            rows.createCell(0).setCellValue(i+1);
            rows.createCell(1).setCellValue(employee.getUsername());
            if (employee.getName()!=null){
                rows.createCell(2).setCellValue(employee.getName());
            }else{
                rows.createCell(2).setCellValue("");
            }
            if (employee.getPassword()!=null){
                rows.createCell(3).setCellValue(employee.getPassword());
            }else{
                rows.createCell(3).setCellValue("");
            }
            if (employee.getEmail()!=null){
                rows.createCell(4).setCellValue(employee.getEmail());
            }else{
                rows.createCell(4).setCellValue("");
            }
            if (employee.getAge()!=null){
                rows.createCell(5).setCellValue(employee.getAge());
            }else{
                rows.createCell(5).setCellValue(0);
            }
        }

        //将表格返回
        return workbook;
    }

    @Override
    public Workbook getWrokbookModel() {
        //创建一个excel表格
        Workbook workbook = new HSSFWorkbook();

        //创建工作区
        Sheet st = workbook.createSheet("员工表");
        Row row = st.createRow(0);
        row.createCell(0).setCellValue("用户名");
        row.createCell(1).setCellValue("姓名");
        row.createCell(2).setCellValue("密码");
        row.createCell(3).setCellValue("邮箱");
        row.createCell(4).setCellValue("年龄");
        return workbook;
    }

    @Override
    public void importXls(MultipartFile file)throws IOException {
        //模板的解析
        HSSFWorkbook sheets = new HSSFWorkbook(file.getInputStream());
        Sheet sheetAt = sheets.getSheetAt(0);
        int lastRowNum = sheetAt.getLastRowNum();

        for (int i = 1; i <=lastRowNum; i++) {
            Employee employee = new Employee();
            Row row = sheetAt.getRow(i);
                if (row.getCell(0).getCellType() == CellType.NUMERIC){
                    double numericCellValue = row.getCell(0).getNumericCellValue();
                    employee.setUsername("" + numericCellValue);
                }else {
                    //如果以后输入的不是数字
                    String cell0 = row.getCell(0).getStringCellValue();
                    employee.setUsername("" + cell0);
                }

                if (row.getCell(1).getCellType() == CellType.NUMERIC){
                    double numericCellValue1 = row.getCell(1).getNumericCellValue();
                    employee.setName("" + numericCellValue1);
                }else {
                    //如果以后输入的不是数字
                    String cell1 = row.getCell(1).getStringCellValue();
                    employee.setName("" + cell1);
                }

                if (row.getCell(2).getCellType() == CellType.NUMERIC){
                    double numericCellValue2 = row.getCell(2).getNumericCellValue();
                    employee.setPassword("" + numericCellValue2);
                }else {
                    //如果以后输入的不是数字
                    String cell2 = row.getCell(2).getStringCellValue();
                    employee.setPassword("" + cell2);
                }

                if (row.getCell(3).getCellType() == CellType.NUMERIC){
                    double numericCellValue3 = row.getCell(3).getNumericCellValue();
                    employee.setEmail("" + numericCellValue3);
                }else {
                    //如果以后输入的不是数字
                    String cell3 = row.getCell(3).getStringCellValue();
                    employee.setEmail("" + cell3);
                }
                //如果以后输入的不是数字
            double numericCellValue = row.getCell(4).getNumericCellValue();
                employee.setAge((int) numericCellValue);
            insert(employee);
          //  }
        }
        //读取模板内容
        //将数据保存到数据库中
    }
}
